package wstrader.task.home;

import java.sql.ResultSet;
import java.sql.SQLException;

import pc.system.*;
import wstrader.task.trade.*;

public class DailyBasis extends TradeTask
{
  public wstrader.task.home.physical.DailyBasis physical_daily_basis=null;
  public wstrader.task.home.futures.DailyBasis futures_daily_basis=null;
  public DailyBasis(Tasks owner)
  {
    super(owner,"daily-basis-t");
    physical_daily_basis = new wstrader.task.home.physical.DailyBasis(this);
    futures_daily_basis = new wstrader.task.home.futures.DailyBasis(this);
  }
  
  public ResultSet getGradeList()
  {
  	String Sql = "select id,name from grade_master order by id";
  	return getSQLCommand().executeQuery(Sql);
  }
  
  public float getSumDBReport(String Tbl_name,String FieldName,int Qid,int Gid, java.sql.Date StDt,java.sql.Date Endt,String CType)
  {
    float val = 0;
    String sql="";
    int OriginId = getRequest().getIntValue("origin");
    if (Tbl_name == null || Tbl_name.length() == 0) return 0;
    if (OriginId ==0)
    {
    	sql = "SELECT sum(" +  FieldName + ") as name FROM " + Tbl_name + " where quality_id = " + Qid + " and grade_id = " + Gid + " and shipment_last_date  >= '" + StDt + "' and shipment_last_date < '" + Endt + "' and basis_type = '" + CType + "'";
    }
    else
    {
    	sql = "SELECT sum(" +  FieldName + ") as name FROM " + Tbl_name + " where quality_id = " + Qid + " and grade_id = " + Gid + " and shipment_last_date  >= '" + StDt + "' and shipment_last_date < '" + Endt + "' and basis_type = '" + CType + "' and origin_id = " + OriginId;
    }
    ResultSet rs = getSQLCommand().executeQuery(sql);

    try {
    	rs.beforeFirst();
	  	if (rs.next()==true)
	  	{
	  		if (CType.compareTo("P")==0) val = rs.getFloat("name");
	  		if (CType.compareTo("S")==0) val = rs.getFloat("name") * -1;
	  	}
	  	else
	  	{
	  		val=0;
	  		return val;
	  	}
      rs.close();
    } catch (SQLException e) {
      System.out.println(e.getMessage());
      val =0;
      return val;
    }
    return val;
  }  
 
  public float getBestDiffDBReport(String Tbl_name,String FieldName,int Qid,int Gid, java.sql.Date StDt,java.sql.Date Endt,String CType)
  {
  	float val = 0;
    String sql="";
    int OriginId = getRequest().getIntValue("origin");
    
    if (Tbl_name == null || Tbl_name.length() == 0) return 0;
    
    if (CType.compareTo("P")==0)
    {
    	if (OriginId == 0)
    	{
    		sql = "SELECT MIN(" +  FieldName + ") as TotDiff FROM " + Tbl_name + " where quality_id = " + Qid + " and grade_id = " + Gid + " and shipment_last_date  >= '" + StDt + "' and shipment_last_date < '" + Endt + "' and basis_type = '" + CType + "'";
    	}
    	else
    	{
    		sql = "SELECT MIN(" +  FieldName + ") as TotDiff FROM " + Tbl_name + " where quality_id = " + Qid + " and grade_id = " + Gid + " and shipment_last_date  >= '" + StDt + "' and shipment_last_date < '" + Endt + "' and basis_type = '" + CType + "' and origin_id = " + OriginId;
    	}
    }
    
    if (CType.compareTo("S")==0)
    {
    	if (OriginId == 0)
    	{
    		sql = "SELECT MAX(" +  FieldName + ") as TotDiff FROM " + Tbl_name + " where quality_id = " + Qid + " and grade_id = " + Gid + " and shipment_last_date  >= '" + StDt + "' and shipment_last_date < '" + Endt + "' and basis_type = '" + CType + "'";
    	}
    	else
    	{
    		sql = "SELECT MAX(" +  FieldName + ") as TotDiff FROM " + Tbl_name + " where quality_id = " + Qid + " and grade_id = " + Gid + " and shipment_last_date  >= '" + StDt + "' and shipment_last_date < '" + Endt + "' and basis_type = '" + CType + "' and origin_id = " + OriginId;
    	}
    }
    
    ResultSet rs = getSQLCommand().executeQuery(sql);

    try {
    	rs.beforeFirst();
	  	if (rs.next()==true)
	  	{
	  		val = rs.getFloat("TotDiff");
	  	}
	  	else
	  	{
	  		val=0;
	  		return val;
	  	}
      rs.close();
    } catch (SQLException e) {
      System.out.println(e.getMessage());
      val =0;
      return val;
    }
    return val;
  }   
  public float getSumDBReport_Before(String Tbl_name,String FieldName,int Qid,int Gid, java.sql.Date StDt,String CType)
  {
    float val = 0;
    String sql ="";
    int OriginId = getRequest().getIntValue("origin");
    if (Tbl_name == null || Tbl_name.length() == 0) return 0;
    if(OriginId == 0)
    {
    	sql = "SELECT sum(" +  FieldName + ") as name FROM " + Tbl_name + " where quality_id = " + Qid + " and grade_id = " + Gid + " and shipment_last_date  < '" + StDt + "' and basis_type = '" + CType + "'";
    }
    else
    {
    	sql = "SELECT sum(" +  FieldName + ") as name FROM " + Tbl_name + " where quality_id = " + Qid + " and grade_id = " + Gid + " and shipment_last_date  < '" + StDt + "' and basis_type = '" + CType + "' and origin_id = " + OriginId;
    }
    ResultSet rs = getSQLCommand().executeQuery(sql);

    try {
    	rs.beforeFirst();
	  	if (rs.next()==true)
	  	{
	  		if (CType.compareTo("P")==0) val = rs.getFloat("name");
	  		if (CType.compareTo("S")==0) val = rs.getFloat("name") * -1;
	  	}
	  	else
	  	{
	  		val=0;
	  		return val;
	  	}
      rs.close();
    } catch (SQLException e) {
      System.out.println(e.getMessage());
      val =0;
      return val;
    }
    return val;
  }    
  
  public float getSumDBReport_After(String Tbl_name,String FieldName,int Qid,int Gid, java.sql.Date Endt,String CType)
  {
    float val = 0;
    String sql="";
    int OriginId = getRequest().getIntValue("origin");
    if (Tbl_name == null || Tbl_name.length() == 0) return 0;
    
    if (OriginId == 0)
    {
    	sql = "SELECT sum(" +  FieldName + ") as name FROM " + Tbl_name + " where quality_id = " + Qid + " and grade_id = " + Gid + " and shipment_last_date > '" + Endt + "' and basis_type = '" + CType + "'";
    }
    else
    {
    	sql = "SELECT sum(" +  FieldName + ") as name FROM " + Tbl_name + " where quality_id = " + Qid + " and grade_id = " + Gid + " and shipment_last_date > '" + Endt + "' and basis_type = '" + CType + "' and origin_id = " + OriginId;
    }
    ResultSet rs = getSQLCommand().executeQuery(sql);

    try {
    	rs.beforeFirst();
	  	if (rs.next()==true)
	  	{
	  		if (CType.compareTo("P")==0) val = rs.getFloat("name");
	  		if (CType.compareTo("S")==0) val = rs.getFloat("name") * -1;
	  	}
	  	else
	  	{
	  		val=0;
	  		return val;
	  	}
      rs.close();
    } catch (SQLException e) {
      System.out.println(e.getMessage());
      val =0;
      return val;
    }
    return val;
  }
  
  public float getDBChkTon(String Tbl_name,String FieldName,int Qid,int Gid,String CType)
  {
    float val = 0;
    String sql="";
    int OriginId = getRequest().getIntValue("origin");
    if (Tbl_name == null || Tbl_name.length() == 0) return 0;
    if (OriginId == 0)
    {
    	sql = "SELECT sum(" +  FieldName + ") as name FROM " + Tbl_name + " where quality_id = " + Qid + " and grade_id = " + Gid + " and basis_type = '" + CType + "'";
    }
    else
    {
    	sql = "SELECT sum(" +  FieldName + ") as name FROM " + Tbl_name + " where quality_id = " + Qid + " and grade_id = " + Gid + " and basis_type = '" + CType + "' and origin_id = " + OriginId;
    }

    ResultSet rs = getSQLCommand().executeQuery(sql);

    try {
    	rs.beforeFirst();
	  	if (rs.next()==true)
	  	{
	  		val = rs.getFloat("name");
	  	}
	  	else
	  	{
	  		val=0;
	  		return val;
	  	}
      rs.close();
    } catch (SQLException e) {
      System.out.println(e.getMessage());
      val =0;
      return val;
    }
    return val;
  }   
  
  
  public float getDBChkRec(String Tbl_name,String FieldName,int Qid,int Gid,String CType)
  {
    float val = 0;
    String sql="";
    int OriginId = getRequest().getIntValue("origin");
    if (Tbl_name == null || Tbl_name.length() == 0) return 0;
    if (OriginId == 0)
    {
    	sql = "SELECT count(*) as name FROM " + Tbl_name + " where quality_id = " + Qid + " and grade_id = " + Gid + " and basis_type = '" + CType + "'";
    }
    else
    {
    	sql = "SELECT count(*) as name FROM " + Tbl_name + " where quality_id = " + Qid + " and grade_id = " + Gid + " and basis_type = '" + CType + "' and origin_id = " + OriginId;
    }
    ResultSet rs = getSQLCommand().executeQuery(sql);

    try {
    	rs.beforeFirst();
	  	if (rs.next()==true)
	  	{
	  		val = rs.getFloat("name");
	  	}
	  	else
	  	{
	  		val=0;
	  		return val;
	  	}
      rs.close();
    } catch (SQLException e) {
      System.out.println(e.getMessage());
      val =0;
      return val;
    }
    return val;
  }     
}
